﻿using SupermarkedModel;
using System;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using System.Linq;

public static class SalesReportsImporter
{
    public static void ImportSalesReports(string salesReportsZipFile)
    {
        // extract zip 
        FileProcessor fileProcessor = new FileProcessor("TempFolder");
        fileProcessor.UnzipFile(salesReportsZipFile);

        // read all sales report files
        OleDbConnectionStringBuilder csBuider = new OleDbConnectionStringBuilder();
        csBuider.Provider = "Microsoft.ACE.OLEDB.12.0";
        csBuider.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");

        DirectoryInfo salesReportsDirectory = new DirectoryInfo(fileProcessor.TempFolderPath);
        foreach (DirectoryInfo dir in salesReportsDirectory.GetDirectories())
        {
            foreach (FileInfo file in dir.GetFiles())
            {
                csBuider.DataSource = fileProcessor.TempFolderPath + "\\" + dir.Name + "\\" + file.Name;

                SalesReportData salesReport = ReadSalesReport(csBuider, dir.Name);

                //Console.WriteLine(salesReport.SupermarketName);
                //Console.WriteLine(salesReport.SalesDate);
                //Console.WriteLine("ProductID\tQuantity\tUnitPrice\tSum");
                //for (int i = 0; i < salesReport.Data.Count; i++)
                //{
                //    Console.WriteLine(salesReport.Data[i].ProductID + "\t\t" + salesReport.Data[i].Quantity + "\t\t" +
                //    salesReport.Data[i].UnitPrice + "\t\t" + salesReport.Data[i].Sum);
                //}
                //Console.WriteLine();

                //*******************************************
                // TODO - take every salesReport and push its data to SQL Server
                //*******************************************

                SupermarketDbEntities context = new SupermarketDbEntities();
                using (context)
                {
                    var supermarkets = context.Supermarkets.ToDictionary(x => x.SupermarketID, x => x.SupermarketName);

                    if (!supermarkets.ContainsValue(salesReport.SupermarketName))
                    {
                        var newSupermarket = new SupermarkedModel.Supermarket
                        {
                            SupermarketName = salesReport.SupermarketName
                        };

                        context.Supermarkets.Add(newSupermarket);
                        context.SaveChanges();
                    }

                    var currentSupermarket = context.Supermarkets.Select(x => x).Where(x => x.SupermarketName == salesReport.SupermarketName).ToList();

                    //Console.WriteLine(currentSupermarket[0].SupermarketID + "\t" + currentSupermarket[0].SupermarketName);

                    foreach (var soldItem in salesReport.Data)
                    {
                        var newSaleItem = new SupermarkedModel.Report
                        {
                            ProductID = soldItem.ProductID,
                            Quantity = soldItem.Quantity,
                            UnitPrice = soldItem.UnitPrice,
                            Sum = soldItem.Sum,
                            SupermarketID = currentSupermarket[0].SupermarketID,
                            Date = salesReport.SalesDate
                        };

                        context.Reports.Add(newSaleItem);
                    }

                    context.SaveChanges();
                }

            }
        }

        // delete files
        fileProcessor.DeleteTempDirectory();
        fileProcessor.MoveZipFile(salesReportsZipFile);
    }

    // read files
    private static SalesReportData ReadSalesReport(OleDbConnectionStringBuilder csBuider, string directoryName)
    {
        SalesReportData salesData;

        OleDbConnection connection = new OleDbConnection(csBuider.ConnectionString);
        connection.Open();
        using (connection)
        {
            OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM [Sales$]", connection);
            DataTable dt = new DataTable();
            dbAdapter.Fill(dt);

            // supermarket name
            string supermarketName = (string)(dt.Rows[0].ItemArray[0]);

            // date of report
            string dateString = directoryName; // "22-Jul-2011";
            string format = "d-MMM-yyyy";
            CultureInfo provider = CultureInfo.InvariantCulture;
            DateTime dateOfReport = DateTime.ParseExact(dateString, format, provider);

            salesData = new SalesReportData(supermarketName, dateOfReport);

            for (int i = 2; i < dt.Rows.Count - 1; i++)
            {
                int productID = Convert.ToInt32(dt.Rows[i].ItemArray[0]);
                decimal quantity = Convert.ToDecimal(dt.Rows[i].ItemArray[1]);
                decimal unitPrice = Convert.ToDecimal(dt.Rows[i].ItemArray[2]);
                decimal sum = Convert.ToDecimal(dt.Rows[i].ItemArray[3]);

                salesData.AddSale(productID, quantity, unitPrice, sum);
            }
        }

        return salesData;
    }
}
